package com.seed.core.table.impl;

import com.google.common.base.CaseFormat;
import com.seed.core.table.DataBaseTableServiceI;
import com.seed.core.table.entity.DataBaseTable;
import com.seed.core.table.entity.DataBaseTableDescribe;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author gchiaway
 * 日期: 2020-02-12
 * 时间: 18:01
 */
@Service("dataBaseTableService")
@Transactional(rollbackFor = Exception.class)
public class DataBaseTableServiceImpl implements DataBaseTableServiceI {

    /**
     * 数据库名称
     */
    @Value(value = "${code-generator.databasename}")
    private String dataBaseName;
    @Value("${code-generator.url}")
    private String jdbcUrl;
    @Value("${code-generator.username}")
    private String jdbcUsername;
    @Value("${code-generator.password}")
    private String jdbcPassword;
    @Value("${code-generator.driver-class-name}")
    private String jdbcDiverClassName;

    /**
     * 查看数据库名称
     *
     * @return 数据库名称
     */
    @Override
    public String getDataBaseName() {
        return dataBaseName;
    }

    /**
     * 设置数据库名称
     *
     * @param dataBaseName 数据库名称
     */
    @Override
    public void setDataBaseName(String dataBaseName) {
        this.dataBaseName = dataBaseName;
    }

    /**
     * 根据数据库前缀查找符合条件的表
     *
     * @param dataBaseTableDescribe 数据表名称前缀
     * @return 查出的表
     */
    @Override
    public List<DataBaseTable> listDataBaseTablesByTableNamesPrefix(DataBaseTableDescribe dataBaseTableDescribe, String... tableNames) {
        if (!StringUtils.isEmpty(dataBaseName)) {
            String sql;
            if (null == tableNames || tableNames.length == 0) {
                //表的全前缀是由 项目名称+表前缀 组合而成
                String tableNamesPrefix = dataBaseTableDescribe.getProjectName() + dataBaseTableDescribe.getTablePrefix();
                sql = "SELECT TABLE_NAME as tableName, TABLE_COMMENT as tableComment FROM information_schema.TABLES  WHERE table_schema = '" + dataBaseName + "' AND TABLE_NAME LIKE CONCAT(\"" + tableNamesPrefix + "\", \"%\");";
            } else {
                sql = "SELECT TABLE_NAME as tableName, TABLE_COMMENT as tableComment FROM information_schema.TABLES  WHERE table_schema = '" + dataBaseName + "' AND TABLE_NAME IN ( TABLENAMES );";
                StringBuilder stringBuilder = new StringBuilder();
                for (String tableName : tableNames) {
                    stringBuilder.append("'" + tableName + "',");
                }
                sql = sql.replace("TABLENAMES", stringBuilder.substring(0, stringBuilder.length() - 1));
            }
            //根据SQL语句查询表信息
            List<DataBaseTable> dataBaseTableList = this.listDataBaseTablesBySQL(sql);
            //格式化，然后返回
            return this.formatDataBaseTables(dataBaseTableDescribe, dataBaseTableList);
        } else {
            return new ArrayList<>();
        }
    }

    /**
     * 根据SQL语句查询表信息
     *
     * @param sql SQL语句
     * @return 查出的表
     */
    private List<DataBaseTable> listDataBaseTablesBySQL(String sql) {
        try {
            Class.forName(jdbcDiverClassName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return null;
        }
        try (Connection conn = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword);
             Statement statement = conn.createStatement();
             ResultSet rs = statement.executeQuery(sql)) {

            List<DataBaseTable> dataBaseTableList = new ArrayList<>();
            while (rs.next()) {
                DataBaseTable dbTable = new DataBaseTable();
                dbTable.setTableName(rs.getString("tableName"));
                dbTable.setTableComment(rs.getString("tableComment"));
                dataBaseTableList.add(dbTable);
            }
            return dataBaseTableList;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 格式化数据库表
     *
     * @param dataBaseTableDescribe 表前缀
     * @param dataBaseTableList     格式化前的表
     * @return 格式化完成后的表
     */
    private List<DataBaseTable> formatDataBaseTables(DataBaseTableDescribe dataBaseTableDescribe, List<DataBaseTable> dataBaseTableList) {
        for (DataBaseTable dataBaseTable : dataBaseTableList) {
            //eg: 全表名为 seed_t_test_table

            //项目名称 eg: seed_
            dataBaseTable.setProjectName(dataBaseTableDescribe.getProjectName());
            //表前缀 eg: t_
            dataBaseTable.setTablePrefix(dataBaseTableDescribe.getTablePrefix());

            //带前缀的小写名称 eg: seedttesttable
            dataBaseTable.setTableNameWithOutUnderline(dataBaseTable.getTableName().replaceAll("_", ""));

            //表的全前缀是由 项目名称+表前缀 组合而成 eg: seed_t_
            String tableNamesPrefix = dataBaseTableDescribe.getProjectName() + dataBaseTableDescribe.getTablePrefix();
            //实体名称 eg: test_table
            String entityName = dataBaseTable.getTableName().replaceAll(tableNamesPrefix, "");
            dataBaseTable.setEntityName(entityName);
            //实体名称没有下划线 eg: testtable
            dataBaseTable.setEntityNameWithOutUnderline(entityName.replaceAll("_", ""));

            //实体驼峰名称 eg: TestTable
            String entityNameHump = this.tableNameConvertUpperCamel(entityName);
            dataBaseTable.setEntityNameHump(entityNameHump);
            //实体驼峰名称首字母小写 eg: testTable
            String entityNameHumpFirstLow = this.tableNameConvertLowerCamel(entityName);
            dataBaseTable.setEntityNameHumpFirstLow(entityNameHumpFirstLow);
        }
        return dataBaseTableList;
    }

    /**
     * 驼峰名称
     *
     * @param tableName training_course
     * @return TrainingCourse
     */
    private String tableNameConvertUpperCamel(String tableName) {
        return CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.UPPER_CAMEL, tableName.toLowerCase());
    }

    /**
     * 驼峰名称
     *
     * @param tableName training_course
     * @return trainingCourse
     */
    private String tableNameConvertLowerCamel(String tableName) {
        return CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, tableName.toLowerCase());
    }


}
